package com.dotflat.satistics.repository.crm.impl;

import com.dotflat.satistics.cnst.CommonConstant;
import com.dotflat.satistics.repository.crm.BudgetRepository;

import com.matt.foundation.constant.ContractConstant;
import com.matt.foundation.utils.DateUtil;
import com.matt.repository.base.BaseNativeSqlRepository;
import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Objects;

/**
 * @Describle 预算
 */
@Repository
public class BudgetRepositoryImpl extends BaseNativeSqlRepository implements BudgetRepository {

//    @Autowired
    @Autowired
    private JdbcTemplate crmJdbcTemplate;

    @Override
    public Double findActualFeeByMonthAndAreaId(String month, String areaId) {
        Double count = crmJdbcTemplate.queryForObject("select sum(realPay) as count from budget_income " +
                " where  DATE_FORMAT(`month`,'%Y-%m') = ? AND  areaId=? ", new Object[]{month.substring(0,7), areaId}, Double.class);
        return Objects.isNull(count) ? 0d : count;
    }

    @Override
    public Double findPlanFeeByMonthAndAreaId(String month, String areaId) {
        Double count = crmJdbcTemplate.queryForObject("select sum(planPay) as count from budget_income " +
                " where  DATE_FORMAT(`month`,'%Y-%m') = ? AND  areaId=? ", new Object[]{month.substring(0,7), areaId}, Double.class);
        return Objects.isNull(count) ? 0d : count;
    }

    @Override
    public Double findFeeByParentIdAndTypeAndMonthAndArea(int feeType, int leadsType, String month, String areaId) {
        Double count = crmJdbcTemplate.queryForObject("select sum(realPay) as count from budget_income " +
                " where  DATE_FORMAT(`month`,'%Y-%m') = ? AND  areaId=? and parentid=? and type=? ", new Object[]{month.substring(0,7), areaId,feeType,leadsType}, Double.class);
        return Objects.isNull(count) ? 0d : count;
    }

    @Override
    public List<Map<String, Object>> getAllBudGetByParentId(String month, Integer parentId,String areaIds,Integer timeType){
        String parentSql = "";
        if(parentId != 0){
            parentSql = String.format(" and parentId = %s ",parentId);
        }
        String cateSql = "IFNULL(sum(planPay),0)";
        if(timeType == CommonConstant.WEEK_UNIT){
            cateSql = "IFNULL(ROUND( sum( planPay ) / 4, 2 ),0)";
        }
        String sql = String.format("SELECT %s as count,areaId from budget_income where YEAR(month) = %s AND MONTH(month) = %s " +
                " and areaId in (%s) %s GROUP BY areaId", new Object[]{cateSql,month.substring(0,4), month.substring(5,7),areaIds,parentSql});
        List<Map<String, Object>> maps = crmJdbcTemplate.queryForList(sql);
        if(maps.size() > 0){
            Map<String, Object> map = maps.get(0);
            Object areaId = map.get("areaId");
            if( null == areaId){
                return null;
            }
        }
        return maps;
    }

    @Override
    public List<Map<String, Object>> getAllBudGetRealByParentId(String month, Integer parentId,String areaIds,Integer timeType){
        String parentSql = "";
        if(parentId != 0){
            parentSql = String.format(" and parentId = %s ",parentId);
        }
        String cateSql = "IFNULL(sum(realPay),0)";
        if(timeType == CommonConstant.WEEK_UNIT){
            cateSql = "IFNULL(ROUND(sum(realPay)/4,2),0)";
        }
        if(timeType == CommonConstant.DAY_UNIT){
            int daysOfMonth = DateUtil.getDaysOfMonth(month);
            cateSql = "IFNULL(ROUND(sum(realPay)/"+daysOfMonth+",2),0)";
        }
        String sql = String.format("SELECT %s as count,areaId from budget_income where YEAR(month) = %s AND MONTH(month) = %s " +
                " and areaId in (%s) %s GROUP BY areaId", new Object[]{cateSql,month.substring(0,4), month.substring(5,7),areaIds,parentSql});
        List<Map<String, Object>> maps = crmJdbcTemplate.queryForList(sql);
        return maps;
    }

    @Override
    public Object getAllBudGetRealByArea(String month, Integer parentId, String areaId, Integer timeType){
        String parentSql = "";
        String sql = "";
        if(parentId != 0){
            parentSql = String.format(" and parentId = %s ",parentId);
        }
        if(timeType == CommonConstant.WEEK_UNIT){
            String cateSql = "IFNULL(sum(realPay),0)";
            sql = String.format("SELECT %s as count from budget_income where YEAR(month) = %s AND MONTH(month) = %s " +
                    " and areaId = (%s) %s", new Object[]{cateSql, month.substring(0, 4), month.substring(5, 7), areaId, parentSql});
        }

        if(timeType == CommonConstant.WEEK_UNIT){
            String cateSql = "IFNULL(ROUND(sum(realPay)/4,2),0)";
            sql = String.format("SELECT %s as count from budget_income where YEAR(month) = %s AND MONTH(month) = %s " +
                    " and areaId = (%s) %s", new Object[]{cateSql, month.substring(0, 4), month.substring(5, 7), areaId, parentSql});
        }
        if(timeType == CommonConstant.DAY_UNIT){
            int daysOfMonth = DateUtil.getDaysOfMonth(month);
            String cateSql = "IFNULL(ROUND(sum(realPay)/"+daysOfMonth+",2),0)";
            sql = String.format("SELECT %s as count from budget_income where YEAR(month) = %s AND MONTH(month) = %s " +
                    " and areaId = (%s) %s", new Object[]{cateSql, month.substring(0, 4), month.substring(5, 7), areaId, parentSql});
            List<Map<String, Object>> maps = crmJdbcTemplate.queryForList(sql);
            return maps;

        }
        Double count = crmJdbcTemplate.queryForObject(sql, Double.class);
        return count == null ? 0L : count;

    }

    @Override
    public List<Map<String, Object>> getSumBudGetSingle(String month, String areaId) {
        if(areaId.indexOf("'") == -1){
            areaId = "'" + areaId + "'";
        }
        String sql = String.format("SELECT DISTINCT(id), sum(planPay) as planPay,sum(realPay) as realPay, parentid,parentVal from budget_income" +
                " where YEAR(month) = %s and MONTH(month) = %s and areaId in (%s) GROUP BY parentid ORDER BY parentid asc", new Object[]{month.substring(0,4), month.substring(5,7),areaId});
        List<Map<String, Object>> maps = crmJdbcTemplate.queryForList(sql);
        if(maps.size() > 0){
            Map<String, Object> map = maps.get(0);
            String id = map.get("id").toString();
            if(StringUtils.isBlank(id)){
                return null;
            }
        }
        return maps;
    }

    @Override
    public Double findFeeByMonthAndAreaIdAndParentIdAndType(String time, String areaId, Integer parentId,Integer type) {
        String sql = "select sum(realPay) as count from budget_income where DATE_FORMAT(`month`,'%Y-%m') = ? AND  areaId=? ";
        if (parentId !=null && parentId > 0)
            sql +=" and parentid="+parentId;
        if (type !=null && type > 0)
            sql +=" and type="+type;
        Double count = crmJdbcTemplate.queryForObject(sql, new Object[]{time.substring(0,7), areaId}, Double.class);
        return Objects.isNull(count) ? 0d : count;
    }

    @Override
    public Double findActualRunCostFeeByMonthAndAreaIdAndParentId(String time, String areaId, int parentId) {
        Double count = crmJdbcTemplate.queryForObject("select sum(realPay) as count from budget_income " +
                " where  DATE_FORMAT(`month`,'%Y-%m') = ? AND  areaId=? and parentid=? ", new Object[]{time.substring(0,7), areaId,parentId}, Double.class);
        return Objects.isNull(count) ? 0d : count;
    }

    @Override
    public Object checkBudGet(String month, String areaId, Integer type, String templeteId) {
        String typeSql = "";
        if (type != 0) typeSql = " and parentid = " + type;
        String tempSql = "";
        if (StringUtils.isNotEmpty(templeteId)) tempSql = " and templeteId = '" + templeteId + "' ";
        List<Map<String, Object>> maps = crmJdbcTemplate.queryForList("SELECT id,parentid from budget_income where areaId = ? and DATE_FORMAT(month,'%Y-%m') = ?  " + typeSql + tempSql + " GROUP BY parentid ",
                new Object[]{areaId, month});
        return type == 0 ? maps : maps.size() > 0;
    }

    @Override
    public List<Map<String, Object>> getAllDischs(String areaIds,Integer parentId) {
        String sql = String.format("SELECT id as templeteId,name,areaId FROM budget_templete where parentId = %s and (areaId in (%s) or areaId is null ) and isCrm = 0", parentId,areaIds);
        List<Map<String, Object>> maps = crmJdbcTemplate.queryForList(sql);
        return maps;
    }

    @Override
    public List<Map<String, Object>> getAllDitchSum(String month, Integer parentId,String areaIds,Integer timeType,String templeteId){
        String parentSql = "";
        if(parentId != 0){
            parentSql = String.format(" and parentId = %s ",parentId);
        }
        String cateSql = "IFNULL(sum(realPay),0)";
        if(timeType == CommonConstant.WEEK_UNIT){
            cateSql = "IFNULL(ROUND(sum(realPay)/4,2),0)";
        }
        if(timeType == CommonConstant.DAY_UNIT){
            int daysOfMonth = DateUtil.getDaysOfMonth(month);
            cateSql = "IFNULL(ROUND(sum(realPay)/"+daysOfMonth+",2),0)";
        }
        String sql = String.format("SELECT %s as count,areaId from budget_income where YEAR(month) = %s AND MONTH(month) = %s " +
                " and areaId in (%s) %s and templeteId = '%s' GROUP BY areaId", new Object[]{cateSql,month.substring(0,4), month.substring(5,7),areaIds,parentSql,templeteId});
        List<Map<String, Object>> maps = crmJdbcTemplate.queryForList(sql);
        return maps;
    }

    @Override
    public Object getAllDitchSumByArea(Date begin,Date end, Integer parentId,String areaId,Integer timeType,String templeteId){
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
        String beginOfMonth = simpleDateFormat.format(begin);
        String endOfMonth = simpleDateFormat.format(end);
        String parentSql = "";
        String sql = "";
        if(parentId != 0){
            parentSql = String.format(" and parentId = %s ",parentId);
            sql = String.format("SELECT IFNULL(sum(realPay),0) as count from budget_income where YEAR(month) = %s AND MONTH(month) = %s " +
                    " and areaId = %s %s and templeteId = '%s' ", new Object[]{beginOfMonth.substring(0,4), beginOfMonth.substring(5,7),areaId,parentSql,templeteId});
        }
        if(timeType == CommonConstant.WEEK_UNIT){
            sql = String.format("SELECT IFNULL(ROUND(sum(realPay)/4,2),0) as count from budget_income where YEAR(month) = %s AND MONTH(month) = %s " +
                    " and areaId = %s %s and templeteId = '%s' ", new Object[]{beginOfMonth.substring(0,4), beginOfMonth.substring(5,7),areaId,parentSql,templeteId});
        }
        if(timeType == CommonConstant.DAY_UNIT){
            int daysOfMonth = DateUtil.getDaysOfMonth(simpleDateFormat.format(begin).substring(0,7));
            String cateSql = "IFNULL(ROUND(sum(realPay)/"+daysOfMonth+",2),0)";
            sql = String.format("SELECT %s as count from budget_income where YEAR(month) = %s AND MONTH(month) = %s " +
                    " and areaId = '%s' %s and templeteId = '%s' ", new Object[]{cateSql,beginOfMonth.substring(0,4), endOfMonth.substring(5,7),areaId,parentSql,templeteId});
            List<Map<String, Object>> maps = crmJdbcTemplate.queryForList(sql);
            return maps;
        }
        Double aDouble = crmJdbcTemplate.queryForObject(sql, Double.class);
        return aDouble == null ? 0d : aDouble;
    }

    @Override
    public Object getAllDitchSumByParentId(Date begin,Date end, Integer parentId,String areaId,Integer timeType){
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
        String beginOfMonth = simpleDateFormat.format(begin);
        String endOfMonth = simpleDateFormat.format(end);
        String parentSql = "";
        String sql = "";
        if(parentId != 0){
            parentSql = String.format(" and parentId = %s ",parentId);
            sql = String.format("SELECT IFNULL(sum(realPay),0) as count from budget_income where YEAR(month) = %s AND MONTH(month) = %s " +
                    " and areaId = %s %s ", new Object[]{beginOfMonth.substring(0,4), beginOfMonth.substring(5,7),areaId,parentSql});
        }
        if(timeType == CommonConstant.WEEK_UNIT){
            sql = String.format("SELECT IFNULL(ROUND(sum(realPay)/4,2),0) as count from budget_income where YEAR(month) = %s AND MONTH(month) = %s " +
                    " and areaId = %s %s ", new Object[]{beginOfMonth.substring(0,4), beginOfMonth.substring(5,7),areaId,parentSql});
        }
        if(timeType == CommonConstant.DAY_UNIT){
            int daysOfMonth = DateUtil.getDaysOfMonth(simpleDateFormat.format(begin).substring(0,7));
            String cateSql = "IFNULL(ROUND(sum(realPay)/"+daysOfMonth+",2),0)";
            sql = String.format("SELECT %s as count from budget_income where YEAR(month) = %s AND MONTH(month) = %s " +
                    " and areaId = '%s' %s ", new Object[]{cateSql,beginOfMonth.substring(0,4), endOfMonth.substring(5,7),areaId,parentSql});
            List<Map<String, Object>> maps = crmJdbcTemplate.queryForList(sql);
            return maps;
        }
        Double aDouble = crmJdbcTemplate.queryForObject(sql, Double.class);
        return aDouble == null ? 0d : aDouble;
    }

    @Override
    public List<Map<String, Object>> getLeadsArriveSum(Date begin, Date end, String areaIds, String templeteId) {
        List<Map<String, Object>> maps = crmJdbcTemplate.queryForList("SELECT count(1) as count,areaId from stat_leads where type = 2 and date(created)>= date(?) " +
                "and date(created) <= date(?) and areaId in (" + areaIds + ") and channelId = ? GROUP BY areaId ", new Object[]{begin, end,templeteId});
        return maps;
    }

    @Override
    public List<Map<String, Object>> getLeadsArriveSumByArea(Date begin, Date end, String areaId, String templeteId) {
        List<Map<String, Object>> maps = crmJdbcTemplate.queryForList("SELECT count( 1 ) AS count ,date(created) as created from stat_leads where type = 2 and date(created)>= date(?) " +
                "and date(created) <= date(?) and areaId = ? and channelId = ? GROUP BY date(created) ", new Object[]{begin, end, areaId,templeteId});
        return maps;
    }

    @Override
    public Long getLeadsArriveSumCountByArea(Date begin, Date end, String areaId, String templeteId) {
        Long aLong = crmJdbcTemplate.queryForObject("SELECT count( 1 ) AS count from stat_leads where type = 2 and created >= date(?) " +
                "and created <= date(?) and areaId = " + areaId + " and  channelId = ?", new Object[]{begin, end,templeteId}, Long.class);

        return aLong == null ? 0L : aLong;
    }

    @Override
    public Object getLeadsFinishCount(Date begin, Date end, String areaId,int dayUnit,String templeteId) {
        if(areaId.indexOf("'") == -1) areaId = "'" + areaId + "'";
        String sql = "";
        if(dayUnit == CommonConstant.DAY_UNIT){
            sql = "SELECT count(1) as count,areaId from stat_leads where type = 3 and date(created)>= date(?) " +
                    "and date(created) <= date(?) and areaId in (" + areaId + ") and channelId = ? GROUP BY areaId ";
            List<Map<String, Object>> maps = crmJdbcTemplate.queryForList(sql, new Object[]{begin, end,templeteId});
            return maps;
        }else{
            sql = "SELECT count(1) as count from stat_leads where type = 3 and date(created)>= date(?) " +
                    " and date(created) <= date(?) and areaId in (" + areaId + ") and channelId = ?";
            Long aLong = crmJdbcTemplate.queryForObject(sql, new Object[]{begin, end,templeteId},Long.class);
            return aLong == null ? 0L : aLong;
        }
    }

    @Override
    public List<Map<String, Object>> getLeadsFinishCountByDate(Date begin, Date end, String areaId, int dayUnit,String templeteId){
        String sql = "SELECT count(1) as count,date(created) as created from stat_leads where type = 3 and date(created)>= date(?) " +
                "and date(created) <= date(?) and areaId = '" + areaId + "' and channelId = ? GROUP BY date(created) ";
        List<Map<String, Object>> maps = crmJdbcTemplate.queryForList(sql, new Object[]{begin, end,templeteId});
        return maps;
    }
}
